package oa.base.hibernate;

import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import oa.base.session.SessionManger;
import oa.base.util.BeanUtils;
import oa.base.util.Constants;
import oa.base.util.DateUtil;
import oa.base.util.GenericsUtils;
import oa.base.util.Page;
import oa.base.util.SqlUtils;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Conjunction;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.engine.SessionFactoryImplementor;
import org.hibernate.engine.SessionImplementor;
import org.hibernate.impl.CriteriaImpl;
import org.hibernate.loader.criteria.CriteriaQueryTranslator;
import org.hibernate.metadata.ClassMetadata;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.util.Assert;
import org.springframework.util.ReflectionUtils;

/**
 * 范型的HibernateDao基类 对返回值作了泛型转换. 并提供分页函数和若干便捷查询方法.
 * 
 * @author lilang
 * @param <E>
 *            实体类名
 */
public class EntityHibernateDao<E> extends HibernateDaoSupport {
	protected final Log log = LogFactory.getLog(getClass());
	/**
	 * Dao所管理的Entity类型.
	 */
	protected Class<?> entityClass;

	/**
	 * 取得entityClass的函数.不支持泛型的子类可以抛开Class entityClass, 重新实现此函数达到相同效果。
	 */
	protected Class<?> getEntityClass() {
		return entityClass;
	}

	public void setEntityClass(Class<?> clazz) {
		this.entityClass = clazz;
	}

	/**
	 * 通过构造函数对entityClass付值
	 */
	public EntityHibernateDao() {
		this.entityClass = GenericsUtils.getGenericClass(getClass());
	}

	/**
	 * 通过id获取实体实例
	 * 
	 * @param id
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public E get(Long id) {
		return (E) getHibernateTemplate().get(entityClass, id);
	}

	/**
	 * 获取实体所有实例
	 * 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<E> list() {
		return getHibernateTemplate().loadAll(entityClass);
	}

	/**
	 * 新增一条实体实例
	 * 
	 * @param o
	 */
	public Long create(BaseClass o) {
		this.initBase(o);
		return (Long) super.getHibernateTemplate().save(o);
	}

	/**
	 * 用户数据初始化
	 * @param user
	 */
	public void initBase(BaseClass user){
		if(null==user.getCreateor()){
			user.setCreateor(SessionManger.getUserId());
		}
		if(null==user.getCreatedt()){
			user.setCreatedt(new Date());
		}
		if(null==user.getOrgSid()){
			user.setOrgSid(SessionManger.getOrgId());
		}
		if(null==user.getStatus()){
			user.setStatus(Constants.STATUS_ONE);
		}
	}
	
	private Object updatebefore(BaseClass user){
		Object obj = this.get(user.getSid());
		//获取以前对象 剔除null 的处理对象
		BeanUtils.copyPropertiesNoNull(user,obj);
		return obj;
	}
	/**
	 * 处理更新 删除 跟 sql
	 * @param sql
	 * lilang 
	 * 2013-6-16下午6:09:49
	 */
	public void updateExecutSQL(String sql){
		Session session = super.getSessionFactory().getCurrentSession();  
		session.beginTransaction();  
		Query query = session.createQuery(sql);  
		query.executeUpdate();  
		session.getTransaction().commit();  
	}
	
	/**
	 * 修改实体实例
	 * 
	 * @param o
	 */
	public void update(BaseClass o) {
		Object obj = this.updatebefore(o);
		super.getHibernateTemplate().update(obj);
	}
	/**
	 * 修改实体实例
	 * 
	 * @param o
	 */
	public void updateSelect(BaseClass o) {
		super.getHibernateTemplate().update(o);
	}

	/**
	 * 新增或修改一条实体实例
	 * 
	 * @param o
	 */
	public void save(BaseClass o) {
		if(null==o.getSid()){
			this.initBase(o);
		}
		getHibernateTemplate().merge(o);
	}

	/**
	 * 删除实体实例
	 * 
	 * @param o
	 */
	public void remove(Object o) {
		getHibernateTemplate().delete(o);
	}
	
	/**
	 * 删除实体实例
	 * 
	 * @param o
	 */
	public void remove(Map<String,Object> maps) {
		String deleteSql = "delete "+entityClass.getSimpleName()+" "+toSqlString(maps);
		this.getSession().createQuery(deleteSql).executeUpdate();
	}

	/**
	 * 通过id删除实体实例
	 * 
	 * @param id
	 */
	public void removeById(Long id) {
		getHibernateTemplate().delete(get(id));
	}

	/**
	 * 消除与 Session 的关联
	 * 
	 * @deprecated
	 * @param entity
	 */
	// 好像用要报错
	public void evict(Object entity) {
		getHibernateTemplate().evict(entity);
	}

	/**
	 * 同步数据
	 */
	public void flush() {
		getHibernateTemplate().flush();
	}

	/**
	 * 批量增加或修改记录
	 * 
	 * @param c
	 */
	public void saveAll(Collection<E> c) {
		int i=0;
		for(E e:c){
		   getHibernateTemplate().saveOrUpdate(e);
		   // 以每50个数据作为一个处理单元
		   if(i%50==0){
		       // 只是将Hibernate缓存中的数据提交到数据库，保持与数据库数据的同步
			   getHibernateTemplate().flush();  
			   // 清除内部缓存的全部数据，及时释放出占用的内存
			   getHibernateTemplate().clear();  
		    }
		   i++;
		}
	}
	
	public void saveList(Collection<E> c){
		int i=0;
		for(E e:c){
		   getHibernateTemplate().save(e);
		   // 以每50个数据作为一个处理单元
		   if(i%50==0){
		       // 只是将Hibernate缓存中的数据提交到数据库，保持与数据库数据的同步
			   getHibernateTemplate().flush();  
			   // 清除内部缓存的全部数据，及时释放出占用的内存
			   getHibernateTemplate().clear();  
		    }
		   i++;
		}
	}

	/**
	 * 批量删除记录
	 * 
	 * @param c
	 */
	public void removeAll(Collection<E> c) {
		super.getHibernateTemplate().deleteAll(c);
	}

	/**
	 * 同步数据，清空缓存
	 */
	public void clear() {
		getHibernateTemplate().clear();
	}

	/**
	 * 通过HSQL进行统计
	 * 
	 * @param hsql
	 * @param values
	 *            可变参数 用户可以如下四种方式使用 dao.getCount(hql); dao.getCount(hql,arg0);
	 *            dao.getCount(hql,arg0,arg1); dao.getCount(hql,new
	 *            Object[arg0,arg1,arg2])
	 * @return
	 */
	public int getCount(String hsql, Object... values) {
		Object c = getHibernateTemplate().find(hsql, values).get(0);

		if (c instanceof Integer) {
			return ((Integer) c).intValue();
		}

		return 0;
	}

	/**
	 * 通过标准API查询数量
	 */
	public int getCount(Criteria criteria) {
		return ((Integer) criteria.setProjection(Projections.rowCount()).uniqueResult()).intValue();
	}
	
	/**
	 * 通过标准API查询数量
	 */
	public Long findByCount(Map<String,Object> filter,Class<?> clazz) {
		if(null==clazz){
			clazz = entityClass;
		}
		Criteria criteria = getSession().createCriteria(clazz);
		filterCriteria(criteria, filter);
		return Long.valueOf(criteria.setProjection(Projections.rowCount()).uniqueResult().toString());
	}
	
	/**
	 * 通过标准API查询数量
	 */
	public int findByCount(Map<String,Object> filter) {
		Criteria criteria = getSession().createCriteria(entityClass);
		filterCriteria(criteria, filter);
		return (Integer) criteria.setProjection(Projections.rowCount()).uniqueResult();
	}

	/**
	 * hql查询.
	 * 
	 * @param hql
	 * @param values
	 *            可变参数 用户可以如下四种方式使用 dao.find(hql); dao.find(hql,arg0);
	 *            dao.find(hql,arg0,arg1); dao.find(hql,new
	 *            Object[arg0,arg1,arg2])
	 * @return
	 */
	public List find(String hql, Object... values) {
		return getHibernateTemplate().find(hql, values);
	}

	/**
	 * 直接通过hql查询，不带参数
	 */
	public List find(String hql) {
		return getHibernateTemplate().find(hql);
	}

	/**
	 * 直接通过hql查询，不带参数
	 */
	public List findSql(String sql,Map<String,String> values,Class<?> clazz) {
		for(Entry<String, String> entry:values.entrySet()){
				sql = sql.replaceAll(entry.getKey(), entry.getValue());
		}
		Query query	= this.getSession().createSQLQuery(sql).addEntity(clazz);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> result = query.list();
		List rsl = new ArrayList();
		for(Map<String,Object> mps:result){
			rsl.add(mps.get(clazz.getSimpleName()));
		}
		return rsl;
	}
	/**
	 * 直接通过sql查询，不带参数
	 */
	public List<Map<String,Object>> findBySqlMap(String sql,Map<String,String> values) {
		for(Entry<String, String> entry:values.entrySet()){
				sql = sql.replaceAll(entry.getKey(), entry.getValue());
		}
		Query query	= this.getSession().createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> result = query.list();
		return result;
	}
	/**
	 * hql查询.
	 * 
	 * @param hql
	 *            使用 named query parameter as
	 *            <tt>from Foo foo where foo.bar = :bar</tt>.
	 * @param param
	 *            a java.util.Map
	 * @return
	 */
	public List find(String hql, Map<String,Object> param) {
		return getSession().createQuery(hql).setProperties(param).list();
	}

	/**
	 * 根据Map中的条件的Criteria查询.
	 * 
	 * @param map
	 *            Map中仅包含条件名与条件值，默认全部相同, 可重载
	 */
	public List<E> find(Map<String,Object> filter) {
		Criteria criteria = createCriteria();
		return find(criteria, filter);
	}
	
	
	@SuppressWarnings("unchecked")
	public List<E> find(Map<String,Object> filter,Map<String,Object> like,Map<String,Object> sort) {
		Criteria criteria = createCriteria(filter, like, sort);
		return criteria.list();
	}
	/**
	 * 
	 * @param filter 条件
	 * @param sort 排序
	 * @return
	 * lilang 
	 * 2013-10-15上午11:37:53
	 */
	@SuppressWarnings("unchecked")
	public List<E> find(Map<String,Object> filter,Map<String,Object> sort) {
		Criteria criteria = getSession().createCriteria(entityClass);
		// criteria.add(Restrictions.allEq(filterMap));
		filterCriteria(criteria, filter);
		sortCriteria(criteria, sort);
		return criteria.list();
	}

	/**
	 * 根据Map中的条件的Criteria查询.
	 * 
	 * @param map
	 *            Map中仅包含条件名与条件值,默认全部相同, 可重载
	 */
	@SuppressWarnings("unchecked")
	public List<E> find(Criteria criteria, Map<String,Object> filter) {
		Assert.notNull(criteria);
		// criteria.add(Restrictions.allEq(filter));
		filterCriteria(criteria, filter);
		return criteria.list();
	}

	/**
	 * 根据属性名和属性值查询对象.
	 * 
	 * @return 符合条件的对象列表
	 */
	@SuppressWarnings("unchecked")
	public List<E> findBy(String key, Object value) {
		Assert.hasText(key);
		Criteria criteria = getSession().createCriteria(entityClass);
		criteria.add(Restrictions.eq(key, value));
		return criteria.list();
	}

	/**
	 * 根据属性名和属性值以Like AnyWhere方式查询对象.
	 */
	@SuppressWarnings("unchecked")
	public List<E> findByLike(String key, String value) {
		Assert.hasText(key);
		Criteria criteria = getSession().createCriteria(entityClass);
		criteria.add(Restrictions.like(key, value, MatchMode.ANYWHERE));
		return criteria.list();
	}

	/**
	 * 判断对象某些属性的值在数据库中是否唯一.
	 * 
	 * @param uniquePropertyNames
	 *            在POJO里不能重复的属性列表,以逗号分割 如"name,loginid,password"
	 */
	public boolean isUnique(Object entity, String names) {
		Assert.hasText(names);
		Criteria criteria = getSession().createCriteria(entityClass).setProjection(Projections.rowCount());
		String[] nameList = names.split(",");
		try {
			// 循环加入
			for (String name : nameList) {
				criteria.add(Restrictions.eq(name, PropertyUtils.getProperty(entity, name)));
			}

			// 以下代码为了如果是update的情况,排除entity自身.
			String idName = getIdName(entityClass);

			// 取得entity的主键值
			Serializable id = getId(entityClass, entity);

			// 如果id!=null,说明对象已存在,该操作为update,加入排除自身的判断
			if (id != null)
				criteria.add(Restrictions.not(Restrictions.eq(idName, id)));
		} catch (Exception e) {
			ReflectionUtils.handleReflectionException(e);
		}

		return (Integer) criteria.uniqueResult() == 0;
	}

	/**
	 * 根据属性名和属性值查询唯一对象.
	 * 
	 * @param key
	 * @param value
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public E getUniqueBy(String key, Object value) {
		Criteria criteria = getSession().createCriteria(getEntityClass());
		criteria.add(Restrictions.eq(key, value));
		return (E) criteria.uniqueResult();
	}
	
	/**
	 * 根据属性名和属性值查询唯一对象.
	 * 
	 * @param key
	 * @param value
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public E getUniqueBy(Map<String,Object> params) {
		Criteria criteria = getSession().createCriteria(getEntityClass());
		filterCriteria(criteria, params);
		return (E) criteria.uniqueResult();
	}


	/**
	 * 根据属性名和属性值查询唯一对象.
	 * 
	 * @deprecated
	 * @param key
	 * @param value
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public E getFirst(String key, Object value) {
		Criteria criteria = getSession().createCriteria(getEntityClass());
		criteria.add(Restrictions.eq(key, value));
		return (E) criteria.setMaxResults(1).list();
	}

	/**
	 * 创建Query对象.
	 * 对于需要first,max,fetchsize,cache,cacheRegion等诸多设置的函数,可以在返回Query后自行设置.
	 * 留意可以连续设置,如下：
	 * 
	 * <pre>
	 * dao.createQuery(hql).setMaxResult(100).setCacheable(true).list();
	 * </pre>
	 * 
	 * 调用方式如下：
	 * 
	 * <pre>
	 *        dao.createQuery(hql)
	 *        dao.createQuery(hql,arg0);
	 *        dao.createQuery(hql,arg0,arg1);
	 *        dao.createQuery(hql,new Object[arg0,arg1,arg2])
	 * </pre>
	 * 
	 * @param values
	 *            可变参数.
	 */
	public Query createQuery(String hql, Object... values) {
		Assert.hasText(hql);
		Query query = getSession().createQuery(hql);
		for (int i = 0; i < values.length; i++) {
			query.setParameter(i, values[i]);
		}
		return query;
	}

	/**
	 * 创建Criteria对象.
	 * 
	 * @param criterions
	 *            可变的Restrictions条件列表
	 */
	public Criteria createCriteria(Criterion... criterions) {
		Criteria criteria = getSession().createCriteria(entityClass);
		for (Criterion c : criterions) {
			criteria.add(c);
		}
		return criteria;
	}

	/**
	 * 创建Criteria对象.
	 * 
	 * @param criterions
	 *            可变的Restrictions条件列表
	 */
	public Criteria createCriteria(Map<String,Object> filterMap, Map<String,Object> likeMap, Map<String,Object> sortMap) {
		Criteria criteria = getSession().createCriteria(entityClass);
		// criteria.add(Restrictions.allEq(filterMap));
		filterCriteria(criteria, filterMap);
		likeCriteria(criteria, likeMap);
		sortCriteria(criteria, sortMap);

		return criteria;
	}

	/**
	 * 生成方便通用的代条件分页查询
	 * 
	 * @param filterMap
	 *            eq条件
	 * @param likeMap
	 *            like条件
	 * @param sortMap
	 *            sort排序条件
	 * @param pageNo
	 *            起始条件
	 * @param pageSize
	 *            页大小
	 * @return
	 */
	public Page pagedQuery(Map<String,Object> filterMap, Map<String,Object> likeMap, Map<String,Object> sortMap, int pageNo, int pageSize) {
		return pagedQuery(createCriteria(filterMap, likeMap, sortMap), pageNo, pageSize);
	}
	
	
	/**
	 * 生成方便通用的代条件分页查询
	 * 
	 * @param filterMap
	 *            eq条件
	 * @param likeMap
	 *            like条件
	 * @param sortMap
	 *            sort排序条件
	 * @param pageNo
	 *            起始条件
	 * @param pageSize
	 *            页大小
	 * @return
	 */
	public Page pagedQuery(Map<String,Object> filterMap,Map<String,Object> sortMap,int pageNo, int pageSize,Class<?> clazz) {
		Criteria criteria = getSession().createCriteria(clazz);
		filterCriteria(criteria, filterMap);
		sortCriteria(criteria, sortMap);
		return pagedQuery(criteria, pageNo, pageSize);
	}
	
	/**
	 * 生成方便通用的代条件分页查询
	 * 
	 * @param filterMap
	 *            eq条件
	 * @param likeMap
	 *            like条件
	 * @param sortMap
	 *            sort排序条件
	 * @param pageNo
	 *            起始条件
	 * @param pageSize
	 *            页大小
	 * @return
	 */
	public Page pagedQuery(Map<String,Object> filterMap,Map<String,Object> sortMap,int pageNo, int pageSize) {
		Criteria criteria = getSession().createCriteria(entityClass);
		filterCriteria(criteria, filterMap);
		sortCriteria(criteria, sortMap);
		return pagedQuery(criteria, pageNo, pageSize);
	}
	
	

	/**
	 * 分页查询函数，使用sql.
	 * 
	 * @param pageNo
	 *            页号,从1开始.
	 */
	public Page pagedSql(String sql, int pageNo, int pageSize,Class<?> claza) {
		return pagedSql(sql, pageNo, pageSize, -1,claza);
	}

	/**
	 * 修正informix的first 错误,原方法起始值为0;
	 * 
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @param startIndex
	 * @return
	 */
	public Page pageSql(String sql, int pageNo, int pageSize, int startIndex) {
		return pageSql(sql, pageNo, pageSize, startIndex, -1);
	}

	/**
	 * 修正informix的first 错误,原方法起始值为0;
	 * 
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @param startIndex
	 * @param totalCount
	 * @return
	 */
	public Page pageSql(String sql, int pageNo, int pageSize, int startIndex, int totalCount) {
		Assert.hasText(sql);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");

		// Count查询
		if (totalCount == -1) {
			String countQueryString = " select count (*) " + SqlUtils.removeSelect(SqlUtils.removeOrders(sql));
			List countlist = getSession().createSQLQuery(countQueryString).list();
			totalCount = ((BigDecimal) countlist.get(0)).intValue();
		}

		// 实际查询返回分页对象
		Query query = getSession().createSQLQuery(sql);
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();

		return new Page(list, startIndex, totalCount, pageSize);
	}

	/**
	 * 分页查询函数，使用sql.
	 * 
	 * @param pageNo
	 *            页号,从1开始.
	 */
	public Page pagedSql(String sql, int pageNo, int pageSize, int totalCount,Class<?> classaz) {
		Assert.hasText(sql);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");

		// Count查询
		if (totalCount == -1) {
			String countQueryString = " select count(1) " + SqlUtils.removeSelect(SqlUtils.removeOrders(sql));
			List countlist = getSession().createSQLQuery(countQueryString).list();
			Object obj = countlist.get(0);
			if(obj instanceof java.math.BigInteger){
				totalCount = ((java.math.BigInteger)obj).intValue();
			}else if(obj instanceof java.math.BigDecimal){
				totalCount = ((BigDecimal)obj).intValue();
			}else{
				totalCount =0;
			}
			
		}

		// 实际查询返回分页对象
		int startIndex = Page.getStartOfPage(pageNo, pageSize);
		Query query;
		if(null==classaz){
			query = getSession().createSQLQuery(sql).addEntity(entityClass);
		}else{
			query = getSession().createSQLQuery(sql).addEntity(classaz);
		}
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<Map<String,Object>> list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();
		List<E> result = new ArrayList<E>();
		for(Map<String,Object> maps:list){
			result.add((E) maps.get(entityClass.getSimpleName()));
		}
		return new Page(result, startIndex, totalCount, pageSize);
	}

	/**
	 * 分页查询函数，使用hql
	 * 
	 * @param hql
	 *            使用JDBC-style query parameter as
	 *            <tt>from Foo foo where foo.bar = ?</tt>.
	 * @param pageNo
	 *            页号,从0开始.
	 * @param pageSize
	 * @param values
	 * @return
	 */
	public Page pagedQuery(String hql, int pageNo, int pageSize, Object... values) {
		return pagedQuery(hql, pageNo, pageSize, -1, values);
	}

	/**
	 * 分页查询函数，使用hql
	 * 
	 * @param hql
	 *            使用JDBC-style query parameter as
	 *            <tt>from Foo foo where foo.bar = ?</tt>.
	 * @param pageNo
	 *            页号,从0开始.
	 * @param pageSize
	 * @param totalCount
	 * @param values
	 * @return
	 */
	public Page pagedQuery(String hql, int pageNo, int pageSize, int totalCount, Object... values) {
		Assert.hasText(hql);
		if (totalCount == -1) {
			String countQueryString = " select count(1) " + SqlUtils.removeSelect(SqlUtils.removeOrders(hql));
			List countlist = getHibernateTemplate().find(countQueryString, values);
			totalCount = ((Long) countlist.get(0)).intValue();
		}

		// 返回分页对象
		if (totalCount < 1)
			return new Page();

		int startIndex = Page.getStartOfPage(pageNo, pageSize);
		// 创建查询
		Query query = createQuery(hql, values);
		List list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();

		return new Page(list, startIndex, totalCount, pageSize);
	}

	/**
	 * 分页查询函数，使用hql
	 * 
	 * @param hql
	 *            使用 named query parameter as
	 *            <tt>from Foo foo where foo.bar = :bar</tt>.
	 * @param pageNo
	 *            页号,从0开始.
	 * @param pageSize
	 *            页大小
	 * @param param
	 *            a java.util.Map
	 * @return
	 */
	public Page pagedQuery(String hql, int pageNo, int pageSize, Map param) {
		return pagedQuery(hql, pageNo, pageSize, -1, param);
	}

	/**
	 * 分页查询函数，使用hql
	 * 
	 * @param hql
	 *            使用 named query parameter as
	 *            <tt>from Foo foo where foo.bar = :bar</tt>.
	 * @param pageNo
	 *            页号,从0开始.
	 * @param pageSize
	 *            页大小
	 * @param param
	 *            a java.util.Map
	 * @return
	 */
	public Page pagedQuery(String hql, int pageNo, int pageSize, int totalCount, Map param) {
		Assert.hasText(hql);
		if (totalCount == -1) {
			String countQueryString = " select count(*) " + SqlUtils.removeSelect(SqlUtils.removeOrders(hql));
			List countlist = getSession().createQuery(countQueryString).setProperties(param).list();
			totalCount = ((Long) countlist.get(0)).intValue();
		}

		// 返回分页对象
		if (totalCount < 1)
			return new Page();

		int startIndex = Page.getStartOfPage(pageNo, pageSize);

		// 创建查询
		Query query = getSession().createQuery(hql).setProperties(param);
		List list = query.setFirstResult(startIndex).setMaxResults(pageSize).list();

		return new Page(list, startIndex, totalCount, pageSize);
	}

	/**
	 * 分页查询函数，使用Criteria
	 * 
	 * @param pageNo
	 *            页号,从0开始.
	 */
	@SuppressWarnings("unchecked")
	public Page pagedQuery(Criteria criteria, int pageNo, int pageSize) {
		Assert.notNull(criteria);
		Assert.isTrue(pageNo >= 1, "pageNo should start from 1");
		CriteriaImpl impl = (CriteriaImpl) criteria;

		// 先把Projection和OrderBy条件取出来,清空两者来执行Count操作
		Projection projection = impl.getProjection();
		List<CriteriaImpl.OrderEntry> orderEntries;
		try {
			orderEntries = (List<CriteriaImpl.OrderEntry>) BeanUtils.forceGetProperty(impl, "orderEntries");
			BeanUtils.forceSetProperty(impl, "orderEntries", new ArrayList());
		} catch (Exception e) {
			throw new InternalError(" Runtime Exception impossibility throw ");
		}
		int totalCount = 0;
		try {
			// 执行查询
			totalCount = (Integer) criteria.setProjection(Projections.rowCount()).uniqueResult();
		} catch (Exception e) {
			System.out.println("E:" + e.getMessage());
		}

		// 将之前的Projection和OrderBy条件重新设回去
		criteria.setProjection(projection);
		if (projection == null) {
			criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		}
		try {
			BeanUtils.forceSetProperty(impl, "orderEntries", orderEntries);
		} catch (Exception e) {
			throw new InternalError(" Runtime Exception impossibility throw ");
		}

		// 返回分页对象
		if (totalCount < 1)
			return new Page();

		int startIndex = Page.getStartOfPage(pageNo, pageSize);

		List list = criteria.setFirstResult(startIndex).setMaxResults(pageSize).list();

		return new Page(list, startIndex, totalCount, pageSize);
	}

	/**
	 * 根据filter 构造criteria的过滤条件的回调函数. 默认将filter内的所有对象设为equal条件, 一般需要在子类进行重载
	 * 
	 * @param criteria
	 *            Criteria实例
	 * @param filter
	 *            查询条件
	 */
	@SuppressWarnings("unchecked")
	protected void filterCriteria(Criteria criteria, Map<String,Object> filter) {
		if (filter != null && !filter.isEmpty()) {
			//HibernateQueryUtil.changeStringToClassFieldTypeOfMap(entityClass, filter, true);
			Conjunction conj = Restrictions.conjunction();
			for(Entry<String, Object> me:filter.entrySet()){
				//如果传入值为空跳过
				if(null==me.getValue()){
					continue;
				}
				if (me.getKey().indexOf(Constants.WHERE_GE) != -1) {
					conj.add(Restrictions.ge(( me.getKey()).replace(Constants.WHERE_GE, ""), me.getValue()));
					continue;
				}
				if (me.getKey().indexOf(Constants.WHERE_LE) != -1) {
					conj.add(Restrictions.le(( me.getKey()).replace(Constants.WHERE_LE, ""), me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_GT) != -1) {
					conj.add(Restrictions.gt(( me.getKey()).replace(Constants.WHERE_GT, ""), me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_LT) != -1) {
					conj.add(Restrictions.lt(( me.getKey()).replace(Constants.WHERE_LT, ""), me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_NE) != -1) {
					conj.add(Restrictions.ne(( me.getKey()).replace(Constants.WHERE_NE, ""), me.getValue()));
					continue;
				}
				
				if (me.getKey().indexOf(Constants.WHERE_OR) != -1) {
					Map<String,Object> val = (Map<String, Object>) me.getValue();
					Criterion lhs = null,rhs=null;
					for(Entry<String, Object> entry:val.entrySet()){
						if(lhs==null){
							lhs = this.filterKey(entry.getKey(), entry.getValue());
						}else{
							rhs = this.filterKey(entry.getKey(), entry.getValue());
						}
					}
					conj.add(Restrictions.or(lhs, rhs));
					continue;
				}
				
				if (me.getKey().indexOf(Constants.WHERE_BETWEEN) != -1) {
					Object[] objects=(Object[]) me.getValue();
					if(objects.length>1){
						conj.add(Restrictions.between((me.getKey()).replace(Constants.WHERE_BETWEEN, ""), objects[0], objects[1]));
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_IN) != -1) {
					if(me.getValue() instanceof Collection){
						List<Long> values = (List<Long>) me.getValue();
						conj.add(Restrictions.in((me.getKey()).replace(Constants.WHERE_IN, ""), values));
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_NOT_IN) != -1) {
					if(me.getValue() instanceof Collection){
						List<Long> values = (List<Long>) me.getValue();
						conj.add(Restrictions.not(Restrictions.in((me.getKey()).replace(Constants.WHERE_IN, ""), values)));
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_LIKE) != -1) {
						String value = (String) me.getValue();
						if (StringUtils.isNotBlank(value)){
							criteria.add(Restrictions.like((me.getKey()).replace(Constants.WHERE_LIKE, ""), value, MatchMode.ANYWHERE));
						}
						continue;
				}
				conj.add(Restrictions.eq(me.getKey(), me.getValue()));
			}
			criteria.add(conj);
		}
	}
	/**
	 * 解析查询条件
	 * @param key
	 * @param val
	 * lilang 
	 * 2013-10-16上午10:36:27
	 */
	private Criterion filterKey(String key,Object val){
		if (key.indexOf(Constants.WHERE_GE) != -1) {
			return Restrictions.ge((key).replace(Constants.WHERE_GE, ""), val);
		}
		if (key.indexOf(Constants.WHERE_LE) != -1) {
			return Restrictions.le(( key).replace(Constants.WHERE_LE, ""), val);
		}

		if (key.indexOf(Constants.WHERE_GT) != -1) {
			return Restrictions.gt(( key).replace(Constants.WHERE_GT, ""), val);
		}

		if (key.indexOf(Constants.WHERE_LT) != -1) {
			return Restrictions.lt(( key).replace(Constants.WHERE_LT, ""), val);
		}

		if (key.indexOf(Constants.WHERE_NE) != -1) {
			return Restrictions.ne(( key).replace(Constants.WHERE_NE, ""), val);
		}
		
		if (key.indexOf(Constants.WHERE_BETWEEN) != -1) {
			Object[] objects=(Object[]) val;
			if(objects.length>1){
				return Restrictions.between((key).replace(Constants.WHERE_BETWEEN, ""), objects[0], objects[1]);
			}
		}
		if (key.indexOf(Constants.WHERE_IN) != -1) {
			if(val instanceof Collection){
				List<Long> values = (List<Long>) val;
				return Restrictions.in((key).replace(Constants.WHERE_IN, ""), values);
			}
		}
		if (key.indexOf(Constants.WHERE_NOT_IN) != -1) {
			if(val instanceof Collection){
				List<Long> values = (List<Long>) val;
				return Restrictions.not(Restrictions.in((key).replace(Constants.WHERE_IN, ""), values));
			}
		}
		if (key.indexOf(Constants.WHERE_LIKE) != -1) {
				String value = (String) val;
				if (StringUtils.isNotBlank(value)){
					return Restrictions.like((key).replace(Constants.WHERE_LIKE, ""), value, MatchMode.ANYWHERE);
				}
		}
		return null;
	}
	
	/**
	 * 根据filter 构造criteria的过滤条件的回调函数. 默认将filter内的所有对象设为like条件, 一般需要在子类进行重载
	 * 
	 * @param criteria
	 *            Criteria实例
	 * @param filter
	 *            查询条件
	 */
	protected void likeCriteria(Criteria criteria, Map<String,Object> filter) {
		if (filter != null && !filter.isEmpty()) {
			for(Entry<String, Object> likes:filter.entrySet()){
				String value = (String) likes.getValue();
				if (StringUtils.isNotBlank(value)){
					criteria.add(Restrictions.like(likes.getKey(), value, MatchMode.ANYWHERE));
				}
			}
			
		}
	}

	/**
	 * 构造Criteria的排序条件。
	 * 
	 * @param sortMap
	 *            排序条件,map中key-字段名 value-排序方式(asc desc)
	 * @param criteria
	 *            Criteria实例
	 */
	protected void sortCriteria(Criteria criteria, Map<String,Object> sortMap) {
		if (sortMap != null && !sortMap.isEmpty()) {
			for(Entry<String, Object> me:sortMap.entrySet()){
				String key = me.getKey();
				//多个数据排序
				if(key.indexOf(Constants.ORDER_BY_ASC)!=-1){
					String[] vals = String.valueOf(me.getValue()).split(",");
					for(String k:vals){
						criteria.addOrder(Order.asc(k));
					}
					
				}else if(key.indexOf(Constants.ORDER_BY_DESC)!=-1){
					String[] vals = String.valueOf(me.getValue()).split(",");
					for(String k:vals){
						criteria.addOrder(Order.desc(k));
					}
				}else{
					// 处理嵌套属性如category.name
					if (key.indexOf('.') != -1) {
						String alias = StringUtils.substringBefore(key, ".");
						criteria.createAlias(alias, alias);
					}
					if ("asc".equalsIgnoreCase(String.valueOf(me.getValue()))) {
						criteria.addOrder(Order.asc(key));
					} else {
						criteria.addOrder(Order.desc(key));
					}
				}
				
				
			}
		}
	}

	/**
	 * 取得对象的主键值,辅助函数.
	 */
	public Serializable getId(Class clazz, Object entity) throws NoSuchMethodException, IllegalAccessException,
			InvocationTargetException {
		Assert.notNull(entity);
		Assert.notNull(clazz);
		return (Serializable) PropertyUtils.getProperty(entity, getIdName(clazz));
	}

	/**
	 * 取得对象的主键名,辅助函数.
	 */
	public String getIdName(Class clazz) {
		Assert.notNull(clazz);
		ClassMetadata meta = getSessionFactory().getClassMetadata(clazz);
		Assert.notNull(meta, "Class " + clazz + " not define in hibernate session factory.");
		String idName = meta.getIdentifierPropertyName();
		Assert.hasText(idName, clazz.getSimpleName() + " has no identifier property define.");
		return idName;
	}
	/**
	 * 根据filter 构造criteria的过滤条件的回调函数. 默认将filter内的所有对象设为equal条件, 一般需要在子类进行重载
	 * 
	 * @param filter
	 *            查询条件
	 */
	@SuppressWarnings("unchecked")
	public String filterMap(Map<String,Object> filter) {
		String sql = "";
		if (filter != null && !filter.isEmpty()) {
			//HibernateQueryUtil.changeStringToClassFieldTypeOfMap(entityClass, filter, true);
			Conjunction conj = Restrictions.conjunction();
			for(Entry<String, Object> me:filter.entrySet()){
				if (me.getKey().indexOf(Constants.WHERE_GE) != -1) {
					conj.add(Restrictions.ge(( me.getKey()).replace(Constants.WHERE_GE, ""), me.getValue()));
					continue;
				}
				if (me.getKey().indexOf(Constants.WHERE_LE) != -1) {
					conj.add(Restrictions.le(( me.getKey()).replace(Constants.WHERE_LE, ""), me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_GT) != -1) {
					conj.add(Restrictions.gt(( me.getKey()).replace(Constants.WHERE_GT, ""), me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_LT) != -1) {
					conj.add(Restrictions.lt(( me.getKey()).replace(Constants.WHERE_LT, ""), me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_NE) != -1) {
					conj.add(Restrictions.ne(( me.getKey()).replace(Constants.WHERE_NE, ""), me.getValue()));
					continue;
				}
				
				if (me.getKey().indexOf(Constants.WHERE_BETWEEN) != -1) {
					Object[] objects=(Object[]) me.getValue();
					if(objects.length>1){
						conj.add(Restrictions.between((me.getKey()).replace(Constants.WHERE_BETWEEN, ""), objects[0], objects[1]));
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_IN) != -1) {
					if(me.getValue() instanceof Collection){
						List<Long> values = (List<Long>) me.getValue();
						conj.add(Restrictions.in((me.getKey()).replace(Constants.WHERE_IN, ""), values));
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_NOT_IN) != -1) {
					if(me.getValue() instanceof Collection){
						List<Long> values = (List<Long>) me.getValue();
						conj.add(Restrictions.not(Restrictions.in((me.getKey()).replace(Constants.WHERE_IN, ""), values)));
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_LIKE) != -1) {
					conj.add(Restrictions.like((me.getKey()).replace(Constants.WHERE_LIKE, ""), String.valueOf(me.getValue()), MatchMode.ANYWHERE));
					continue;
				}
				conj.add(Restrictions.eq(me.getKey(), me.getValue()));
			}
			//System.out.println(conj.toSqlString(getSession().createCriteria(entityClass),getSession().createSQLQuery("")));
			sql = " where " + getEntitySql(conj);
			
		}
		return sql;
	}
	
	
	public String toSqlString(Map<String,Object> filter) {
		
		StringBuffer sql = new StringBuffer();
		if (filter != null && !filter.isEmpty()) {
			sql.append("where 1=1 ");
			for(Entry<String, Object> me:filter.entrySet()){
				if(null==me.getValue()){
					continue;
				}
				if (me.getKey().indexOf(Constants.WHERE_GE) != -1) {
					sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_GE, "")).append(">=").append(convertObjectToString(me.getValue()));
					continue;
				}
				if (me.getKey().indexOf(Constants.WHERE_LE) != -1) {
					sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_LE, "")).append("<=").append(convertObjectToString(me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_GT) != -1) {
					sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_GT, "")).append(">").append(convertObjectToString(me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_LT) != -1) {
					sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_LT, "")).append("<").append(convertObjectToString(me.getValue()));
					continue;
				}

				if (me.getKey().indexOf(Constants.WHERE_NE) != -1) {
					sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_NE, "")).append("!=").append(convertObjectToString(me.getValue()));
					continue;
				}
				
				if (me.getKey().indexOf(Constants.WHERE_BETWEEN) != -1) {
					Object[] objects=(Object[]) me.getValue();
					if(objects.length>1){
						sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_BETWEEN, "")).append("between ").append(convertObjectToString(objects[0]));
						sql.append(" and ").append(convertObjectToString(objects[1]));
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_IN) != -1) {
					if(me.getValue() instanceof Collection){
						sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_IN, "")).append(" in (").append(convertObjectToString(me.getValue())).append(")");
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_NOT_IN) != -1) {
					if(me.getValue() instanceof Collection){
						sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_NOT_IN, "")).append(" not in (").append(convertObjectToString(me.getValue())).append(")");
						continue;
					}
				}
				if (me.getKey().indexOf(Constants.WHERE_LIKE) != -1) {
					sql.append(" and ").append(( me.getKey()).replace(Constants.WHERE_LIKE, "")).append(" like '%").append(String.valueOf(me.getValue())).append("%'");
					continue;
				}
				sql.append(" and ").append(me.getKey()).append("=").append(convertObjectToString(me.getValue()));
			}
		}
		return sql.toString();
	}
	
	/**
	 * 将Object对象转换为String
	 * @param val
	 * @return
	 */
	public String convertObjectToString(Object val){
		String resultString = "";
		//如果为空,则返回NULL
		if(null==val){
			return null;
		}
		//字符串类型
		if(val instanceof String){
			resultString = "'" + val + "'";
		}else if(val instanceof Date){
			//date
			resultString = "'"+DateUtil.getCurrentTime((Date)val, DateUtil.DATE_FORMAT_yyyyMMddHHmmss)+"'";
		}else if(val instanceof Collection){
			StringBuilder sb = new StringBuilder();
			//collection
			Collection<Object> col = ((Collection<Object>)val);
			for (Object object : col) {
				sb.append(convertObjectToString(object)).append(",");
			}
			sb.deleteCharAt(sb.length() - 1);
			resultString = sb.toString();
		}else if(val instanceof Integer || val instanceof Short 
				|| val instanceof Long || val instanceof Double 
				|| val instanceof Float || val instanceof BigDecimal
				|| val instanceof BigInteger){
			//numeric
			resultString = String.valueOf(val);
		}else{
			resultString = "''";
		}
		return resultString;
	}

	public String getEntitySql(Conjunction conjunction){
		Criteria criteria = getSession().createCriteria(entityClass);
		criteria.add(conjunction);
		CriteriaImpl criteriaImpl = (CriteriaImpl) criteria;//转型  
		SessionImplementor session = criteriaImpl.getSession();
		SessionFactoryImplementor factory = session.getFactory();//获取FACTORY  
		CriteriaQueryTranslator translator = new CriteriaQueryTranslator(factory, criteriaImpl, criteriaImpl.getEntityOrClassName(), CriteriaQueryTranslator.ROOT_SQL_ALIAS); 
		return conjunction.toSqlString(criteriaImpl, translator);
	}
	
	
}
